package com.example.mountainwalker.mapper;

import com.example.mountainwalker.entity.ScenicCommentInfo;
import com.example.mountainwalker.vo.ViewScenicComment;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ScenicCommentMapper {

    // 根据ID获取多表联查详情
    @Select("SELECT \n" +
            "    u.user_name AS userName,\n" +           // 使用AS指定别名
            "    u.user_avatar AS userAvatar,\n" +
            "    s.scenic_area_name AS scenicAreaName,\n" +
            "    c.comment_content AS commentContent,\n" +
            "    c.comment_time AS commentTime,\n" +
            "    c.scenic_comment_id AS scenicCommentId,\n" +
            "    c.user_id AS userId,\n" +
            "    c.scenic_spot_id AS scenicSpotId\n" +
            "FROM scenic_comment c\n" +
            "INNER JOIN user_info u ON c.user_id = u.user_id\n" +
            "INNER JOIN scenic_spot s ON c.scenic_spot_id = s.scenic_spot_id\n" +
            "WHERE c.scenic_comment_id = #{id}")
    ViewScenicComment getViewScenicCommentById(Integer id);

    // 添加统计总数方法
    @Select("SELECT COUNT(*) FROM scenic_comment")
    int countScenicCommentInfo();

    @Select("SELECT * FROM scenic_comment WHERE scenic_comment_id = #{id}")
    ScenicCommentInfo getScenicCommentInfoById(Integer id);

    @Select("SELECT * FROM scenic_comment")
    List<ScenicCommentInfo> getScenicCommentInfo();

    @Delete("DELETE FROM scenic_comment WHERE scenic_comment_id = #{id}")
    int deleteScenicCommentInfoById(Integer id);

    @Insert("INSERT INTO scenic_comment (scenic_comment_id, user_id, scenic_spot_id, comment_content, comment_time) " +
            "VALUES (#{scenicCommentId}, #{userId}, #{scenicSpotId}, #{commentContent}, NOW())")
    int insertScenicCommentInfo(ScenicCommentInfo scenicCommentInfo);

    @Update("UPDATE scenic_comment SET " +
            "user_id = #{userId}, " +
            "scenic_spot_id = #{scenicSpotId}, " +
            "comment_content = #{commentContent} " +  // 添加空格
            "WHERE scenic_comment_id = #{scenicCommentId}")
    int updateScenicCommentInfo(ScenicCommentInfo scenicCommentInfo);

    @Select("SELECT \n" +
            "    u.user_name AS userName,\n" +
            "    u.user_avatar AS userAvatar,\n" +
            "    s.scenic_area_name AS scenicAreaName,\n" +
            "    c.comment_content AS commentContent,\n" +
            "    c.comment_time AS commentTime,\n" +
            "    c.scenic_comment_id AS scenicCommentId,\n" +
            "    c.user_id AS userId,\n" +
            "    c.scenic_spot_id AS scenicSpotId\n" +
            "FROM scenic_comment c\n" +
            "INNER JOIN user_info u ON c.user_id = u.user_id\n" +
            "INNER JOIN scenic_spot s ON c.scenic_spot_id = s.scenic_spot_id\n" +
            "ORDER BY c.comment_time DESC\n" +
            "LIMIT #{offset}, #{pageSize}")
    List<ViewScenicComment> getScenicCommentInfoByPage(@Param("offset") int offset,
                                                       @Param("pageSize") int pageSize);
}